using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Web;
using System.Web.Mvc;
using System.Web.Security;
using MvcExtExample.Models;
using Newtonsoft.Json.Linq;

namespace MvcExtExample.Controllers
{
    public class UserManageController : Controller
    {
        private string Password = "123456";
        private string User_ID;
        //public bool insertUser(string txtUserName, string txtDept, string sexValue, string txtID, string txtPhone, string txtAddress)
        //{
        //    Random rd = new Random();
        //    string temp = rd.Next().ToString();
        //    User_ID = temp;
        //    Password = FormsAuthentication.HashPasswordForStoringInConfigFile(Password, "MD5");
        //    bool result = false;
        //    //连接数据库
        //    SqlConnection ts = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ToString());
        //    //打开数据库连接
        //    ts.Open();
        //    //初始化查询语句
        //    SqlCommand it = new SqlCommand("insert into Tb_User([User_ID],[Dep_ID],[User_Name],[Sex],[Password],[Privilege],[ID],[Cellphone],[Address],[Status])values('" + User_ID + "','" + txtDept + "','" + txtUserName + "','" + sexValue + "','" + Password + "',17,'" + txtID + "','" + txtPhone + "','" + txtAddress+"','有效" + "')", ts);
        //    //对SQL Server数据库执行一个transact-SQL语句
        //    int rows = it.ExecuteNonQuery();
        //    if (rows > 0)
        //        result = true;
        //    //关闭数据库连接
        //    ts.Close();
        //    return result;
        //}

        public int insertUser(string txtUserName, string txtDept, string sexValue, string txtID, string txtPhone, string txtAddress)
        {
            Random rd = new Random();
            string temp = rd.Next().ToString();
            User_ID = temp;
            Password = FormsAuthentication.HashPasswordForStoringInConfigFile(Password, "MD5");
            string sql = "insert into Tb_User([User_ID],[Dep_ID],[User_Name],[Sex],[Password],[Privilege],[ID],[Cellphone],[Address],[Status])values('" + User_ID + "','" + txtDept + "','" + txtUserName + "','" + sexValue + "','" + Password + "',17,'" + txtID + "','" + txtPhone + "','" + txtAddress + "','有效" + "')";
            framework.SqlHelperCS.SqlHelper cs = new framework.SqlHelperCS.SqlHelper();
            int result = -1;
            result = cs.RunSQL(sql);
            return result;
        }

        //
        // GET: /UserManage/addAdmin
        public ActionResult insertAdmin(string txtUserName, string txtDept, string sexValue, string txtID, string txtPhone, string txtAddress)
        {
            int result = insertUser(txtUserName, txtDept, sexValue, txtID, txtPhone, txtAddress);
            if (result > 0)
                return Content("插入成功！");
            else
                return Content("插入失败！");
        }

        //
        // GET: /UserManage/addAdmin

        public ActionResult addAdmin()
        {
            return View();
        }

        //
        // GET: /UserManage/addUser/

        public ActionResult addUser()
        {
            return View();
        }

        //
        // GET: /UserManage/manageUser/

        public ActionResult manageUser()
        {
            return View();
        }

        //
        // GET: /UserManage/ModifyPwd/

        public ActionResult ModifyPwd()
        {
            return View();
        }

        public ActionResult updatePwd(string txtOldPwd, string txtNewPwd, string txtConfirmPwd)
        {
            if (txtNewPwd != txtConfirmPwd)
                return Content("两次密码不一致！");
            txtNewPwd = FormsAuthentication.HashPasswordForStoringInConfigFile(txtNewPwd, "MD5");
            bool result = updatePwdDAL(txtOldPwd, txtNewPwd, txtConfirmPwd);
            if (result)
                return Content("更新成功！");
            else
                return Content("更新失败！");
        }

        public bool updatePwdDAL(string txtOldPwd, string txtNewPwd, string txtConfirmPwd)
        {
            bool result = false;
            //连接数据库
            SqlConnection ts = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ToString());
            //打开数据库连接
            ts.Open();
            //初始化查询语句
            SqlCommand it = new SqlCommand("UPDATE [dbo].[Tb_User] SET [Password] = '" + txtNewPwd + "'", ts);
            //对SQL Server数据库执行一个transact-SQL语句
            int rows = it.ExecuteNonQuery();
            if (rows > 0)
                result = true;
            //关闭数据库连接
            ts.Close();
            return result;
        }

        [AcceptVerbs(HttpVerbs.Get)]
        public string UsersQuery()
        {
            int start = 0;
            int.TryParse(Request.Params["start"], out start);
            int TotalRecords = 0;
            string total = "SELECT COUNT(*) FROM [dbo].[Tb_User]";
            StringBuilder strSqlStr = new StringBuilder();
            strSqlStr.Append("SELECT TOP 16 * FROM [Tb_User] WHERE User_ID NOT IN ( SELECT TOP " + start + " User_ID FROM [Tb_User]   ORDER BY User_ID ) ");
            if (Request.Params["filter"] != null)
            {
                string filter = "";
                JArray jaf = JArray.Parse(Request.Params["filter"]);
                foreach (JObject jo in jaf)
                {
                    filter += " AND " + (string)jo["property"] + " LIKE '%" + (string)jo["value"] + "%' ";
                }
                filter = filter.Substring(0, filter.Length - 1);
                strSqlStr.Append(filter);
            }
            strSqlStr.Append(" ORDER BY User_ID");
            framework.SqlHelperCS.SqlHelper cs = new framework.SqlHelperCS.SqlHelper();
            TotalRecords = cs.ReturnSQL(total);
            DataSet DSet = cs.GetDataSet(strSqlStr.ToString());
            if (DSet != null)
            {
                DataTable dt = DSet.Tables[0];
                StringBuilder JsonString = new StringBuilder();
                if (dt != null && dt.Rows.Count > 0)
                {
                    JsonString.Append("{\"total\":" + TotalRecords + ",\"data\":[ ");
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        JsonString.Append("{ ");
                        for (int j = 0; j < dt.Columns.Count; j++)
                        {
                            JsonString.Append("\"active\":true,");
                            if (j < dt.Columns.Count - 1)
                            {
                                JsonString.Append("\"" + dt.Columns[j].ColumnName.ToString() + "\"" + ":" + "\"" + dt.Rows[i][j].ToString() + "\",");
                            }
                            else if (j == dt.Columns.Count - 1)
                            {
                                JsonString.Append("\"" + dt.Columns[j].ColumnName.ToString() + "\"" + ":" + "\"" + dt.Rows[i][j].ToString() + "\"");
                            }
                        }

                        if (i == dt.Rows.Count - 1)
                        {
                            JsonString.Append("} ");
                        }
                        else
                        {
                            JsonString.Append("}, ");
                        }
                    }
                    JsonString.Append("]}");
                    return JsonString.ToString();
                }
                else
                {
                    return null;
                }
            }
            else
            {
                return null;
            }
        }


        //[AcceptVerbs(HttpVerbs.Get)]
        //public JsonResult AdminQuery()
        //{
        //    int start = 0;
        //    int.TryParse(Request.Params["start"], out start);
        //    List<User> litestModel = new List<User>();
        //    framework.JSONHelper jsonHelp = new framework.JSONHelper();
        //    int TotalRecords = 0;
        //    string strSqlStr = "SELECT TOP 25 * FROM [Tb_User] WHERE User_ID NOT IN ( SELECT TOP "+start+" User_ID FROM [Tb_User]  WHERE  [Privilege] = '17'  ORDER BY User_ID ) AND [dbo].[Tb_User].[Privilege] = '17' ORDER BY User_ID";
        //    framework.SqlHelperCS.SqlHelper cs = new framework.SqlHelperCS.SqlHelper();
        //    DataSet DSet = cs.GetDataSet(strSqlStr);
        //    if (DSet != null)
        //    {
        //        DataTable DTable = DSet.Tables[0];
        //        for (int i = 0; i < DTable.Rows.Count; i++)
        //        {
        //            DataRowView rowview = DSet.Tables[0].DefaultView[i];
        //            litestModel.Add(new User()
        //            {
        //                active = true,//默认为勾选状态
        //                Dep_ID = rowview["Dep_ID"].ToString(),
        //                User_Name = rowview["User_Name"].ToString(),
        //                Sex = rowview["Sex"].ToString(),
        //                User_ID = rowview["User_ID"].ToString(),
        //                Privilege = Convert.ToInt32(rowview["Privilege"].ToString()),
        //                ID = rowview["ID"].ToString(),
        //                Cellphone = rowview["Cellphone"].ToString(),
        //                Address = rowview["Address"].ToString(),
        //                Status = rowview["Status"].ToString()
        //            });
        //        }
        //        return Json(litestModel, JsonRequestBehavior.AllowGet);
        //    }
        //    else
        //    {
        //        return null;
        //    }
        //}

    }
}
